package com.zhishusoft.wordtemplatedemo.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.springframework.stereotype.Service;

import com.zhishusoft.wordtemplatedemo.controller.FileController;
import com.zhishusoft.wordtemplatedemo.tool.WordTemplate;

import lombok.extern.slf4j.Slf4j;

@Service
@Slf4j
public class WordTempService {
	
	
	
	public String execute(String url, String username, String password, Map<String, Object> tables, String parametersMap) {
		
		Map<String, Object> wordDataMap = new HashMap<String, Object>();// 存储报表全部数据
		Map<String, Object> parametersMap1 = new HashMap<String, Object>();// 存储报表中不循环的数据
		
		String driver = "oracle.jdbc.driver.OracleDriver";
		
		try {
			//加载驱动
			Class.forName(driver);
			//建立连接
			Connection conn = DriverManager.getConnection(url, username, password);
		
			//通过数据库的连接操作数据库，实现增删改查（使用Statement类）
			Statement st= conn.createStatement();
			
			//报表循环的数据
			Iterator<Entry<String, Object>>  iterator = tables.entrySet().iterator();
			while(iterator.hasNext()) {
				//建表
				List<Map<String, Object>> table1 = new ArrayList<Map<String, Object>>();
				Entry<String, Object> entry = iterator.next();
				String table = entry.getKey();
				String sql = (String)entry.getValue();
				//get sql 的 字段名select name,age,email from project
//				String[] columnLabels = sql.substring(6, sql.indexOf("from")).trim().split(",");
				ResultSet rs=st.executeQuery(sql);
				//把结果集（map）封装到list中为一个表
				while(rs.next()) {
					Map<String, Object> map1=new HashMap<>();
					ResultSetMetaData metaData = rs.getMetaData();
					for(int i=1;i<=metaData.getColumnCount();i++) {
						String key = metaData.getColumnName(i);
						map1.put(key, rs.getString(key));
					}
//					for(String key:columnLabels) {
//						map1.put(key, rs.getString(key));
//					}
					table1.add(map1);
				}
				wordDataMap.put(table, table1);
				rs.close();
			}
			
			
			//报表不循环的数据
			// get sql 的 字段名select name,age,email from project
//			String[] pcolumnLabels = parametersMap.substring(6, parametersMap.indexOf("from")).trim().split(",");
			ResultSet prs = st.executeQuery(parametersMap);
			// 把结果集（map）封装到list中为一个表
			while (prs.next()) {
				ResultSetMetaData metaData = prs.getMetaData();
				for(int i=1;i<=metaData.getColumnCount();i++) {
					String key = metaData.getColumnName(i);
					parametersMap1.put(key, prs.getString(key));
				}
//				for (String key : pcolumnLabels) {
//					parametersMap1.put(key, prs.getString(key));
//				}
			}
			wordDataMap.put("parametersMap", parametersMap1);
			//关闭连接
			prs.close();
			st.close();
			conn.close();
			
			// 读取word模板
//			String fileName = FileNameContext.getFileName();
			String fileName = FileController.currtFileName;
			File file = new File(fileName + ".docx");//改成你本地文件所在目录
			if(!file.exists()) {
				log.info("模版未上传");
				return "fail";
			}
			FileInputStream fileInputStream = new FileInputStream(file);
			WordTemplate template = new WordTemplate(fileInputStream);

			// 替换数据
			template.replaceDocument(wordDataMap);

			//生成文件
			String newNameFile = fileName + System.currentTimeMillis() + ".docx";
			File outputFile=new File(newNameFile);//改成你本地文件所在目录
			FileOutputStream fos  = new FileOutputStream(outputFile);
			template.getDocument().write(fos);
			return newNameFile;
		} catch (Exception e) {
			log.error("连接异常");
			e.printStackTrace();
		}
		return "fail";
	}

}
